package com.acce.product.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.acce.product.pojo.Goods;

public class ExcelUtils {

	private Logger logger = Logger.getLogger(ExcelUtils.class);

	DateFormat format = new SimpleDateFormat("YYYY-MM-DD HH:mm:ss");

	/**
	 * 读取2010格式excel 待改进成通用工具方法
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 */
	public List<Goods> readXlsxGoods(String filePath) throws IOException {
		InputStream is = new FileInputStream(filePath);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		List<Goods> list = new ArrayList<Goods>();
		String currTime = Utils.getCurrTimeMillions();
		for (int sheetNum = 0; sheetNum < xssfWorkbook.getNumberOfSheets(); sheetNum++) {
			XSSFSheet sheet = xssfWorkbook.getSheetAt(sheetNum);
			if (sheet == null) {
				continue;
			}

			logger.info("read sheet :" + (sheetNum + 1) + "-- total row:"
					+ sheet.getLastRowNum());
			XSSFRow titleRow = sheet.getRow(0);
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				XSSFRow row = sheet.getRow(rowNum);
				if (row != null) {
					Goods goods = null;
					Class<Goods> clazz = null;
					try {
						clazz = (Class<Goods>) Class
								.forName("com.acce.product.pojo.Goods");
						goods = (Goods) clazz.newInstance();
					} catch (Exception e) {
						e.printStackTrace();
					}
					for (int i = 0; i <= row.getLastCellNum(); i++) {
						XSSFCell titleCell = titleRow.getCell(i);
						XSSFCell cell = row.getCell(i);
						String attrValue = getValue(cell);
						String attrName = titleCell.getStringCellValue();
						attrName = attrName.substring(0, 1).toUpperCase()
								+ attrName.substring(1);
						Method mth = null;
						try {
							mth = clazz.getMethod("set" + attrName,
									String.class);
							mth.invoke(goods, attrValue);
						} catch (Exception ex) {
							ex.printStackTrace();
							break;
						}
					}
					if (!"".equals(goods.getGoodsId().trim())) {
						goods.setCreateTime(currTime);
						goods.setSyncTime(currTime);
						goods.setSyncType("1");
						goods.setOriginalNum(goods.getGoodsId());
						list.add(goods);
					}
				}
			}
		}
		return list;
	}

	private String getValue(XSSFCell cell) {
		if (cell != null) {
			if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return format.format(cell.getDateCellValue());
				} else {
					DecimalFormat df = new DecimalFormat("########");

			        return df.format(cell.getNumericCellValue());  
				}
			} else {
				return String.valueOf(cell.getStringCellValue());
			}
		} else {
			return "";
		}
	}

	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	public static void main(String[] args) throws IOException {
		// String path =
		// ExcelUtils.class.getClassLoader().getResource("/init/goods_rawSelect.xlsx").getPath();
		String path = "src/main/resources/init/goods_rawSelect.xlsx";
		ExcelUtils eu = new ExcelUtils();
		List<Goods> list = eu.readXlsxGoods(path);
		System.out.println(list.size());
	}
}
